package org.alex.sample.utils;

import java.io.InputStream;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.alex.sample.model.User;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtils {
	
	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2007_POSTFIX = "xlsx";

	//根据InputStream和Excel类型创建Excel Workbook
	public static Workbook openWorkbook(InputStream is, String postfix) {
		Workbook hssfWorkbook = null;
		try {
			if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
				hssfWorkbook = new HSSFWorkbook(is); //建立新Workbook对象
			}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)) {
				hssfWorkbook = new XSSFWorkbook(is);
			}
		}catch(Exception e) {
			if(e instanceof NotOLE2FileException) {
				return null;
			}
			e.printStackTrace();
		}
		return hssfWorkbook;
	}
	
	//获取指定Excel表格的内容
	public static List<User> getStudentListByExcel(Workbook wk){
		final String GENDER_MALE_STRING = "男", GENDER_FEMALE_STRING = "女";
		final Integer GENDER_MALE_INTEGER = 0, GENDER_FEMALE_INTEGER = 1;
		List<User> resultList = new ArrayList<User>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		User user = null;
		for(int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber++) {
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			user = new User();
			for(int currentCellNumber = 0; currentCellNumber <= maxCellNumber; currentCellNumber++) {
				Cell cell = row.getCell(currentCellNumber);
				switch(currentCellNumber) {
					case 0:
						//获取学号列数据
						user.setUserName(getCellValue(cell));
						break;
					case 1:
						//获取姓名列数据
						user.setName(getCellValue(cell));
						break;
					case 2:
						//获取性别列数据
						String gender = (String) getCellValue(cell);
						//判断值为“男”则存入0，值为女则存入1
						if(GENDER_MALE_STRING.equals(gender)) {
							user.setGender(GENDER_MALE_INTEGER + "");//男
						}else if(GENDER_FEMALE_STRING.equals(gender)) {
							user.setGender(GENDER_FEMALE_INTEGER + "");//女
						}
						break;
					case 3:
						//获取联系电话列数据
						String tel = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue()));
						user.setTel(tel);
						break;
					case 4:
						//获取电子邮箱
						user.setEmail(getCellValue(cell));
						break;
					case 5:
						//获取地址列数据
						user.setAddress(getCellValue(cell));
						break;
					case 6:
						//获取出生日期列数据
						user.setBirthday(getCellValue(cell));
						break;
					case 7:
						//获取密码
						String decodingPassword = (getCellValue(cell));
						user.setPassword(MD5.getMD5Code(decodingPassword));
						break;
				}
			}
			resultList.add(user);
		}
		return resultList;
	}
	
	@SuppressWarnings("static-access")
	private static String getCellValue(Cell cell) {
		if(cell == null) {
			return "";
		}
		if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
			String s = String.valueOf(cell.getDateCellValue());
			return String.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()));
		}else {
			return cell.getStringCellValue();
		}
	}
	
}
